from connsqlite.connect_sqlite3 import Sqlite3Database

"""
 国家统计 详细信息
"""


class RapidCountryManager:
    def __init__(self):
        self.db_name = "rapid.db"
        self.table_name = "rapid_country_info"
        self.columns = "id INTEGER PRIMARY KEY, " \
                       "country TEXT(100), " \
                       "detail TEXT(300), " \
                       "bin_numbers TEXT(100), " \
                       "visa TEXT(100), " \
                       "amex TEXT(100), " \
                       "master_card TEXT(100), " \
                       "discover TEXT(100), " \
                       "other_brands TEXT(100), " \
                       "country_abbr TEXT(100), " \
                       "create_time TEXT(100) DEFAULT CURRENT_TIMESTAMP, " \
                       "update_time TEXT(100) DEFAULT CURRENT_TIMESTAMP, " \
                       "CONSTRAINT unique_country UNIQUE (country,country_abbr)"
        self.sqlite3 = Sqlite3Database(self.db_name)
        self.sqlite3.create_table(self.table_name, self.columns)

    def selectDataByPager(self, offset, pageSize, query_columns=None):
        """
        分页查询列表
        :param offset: 分页
        :param pageSize: 分页数
        :param query_columns: 需要查询列
        :return:
        """
        limit = f" LIMIT {pageSize} OFFSET {offset} "
        return self.sqlite3.select_data_by_limit(self.table_name, columns=query_columns, limit=limit)
        # new_list_of_tuples = []
        # if sql_data is not None:
        #     for t in sql_data:
        #         ts = (t[1], t[2], t[4], t[5], [8], t[9], t[12], t[14], t[17], t[23], t[24], t[28])
        #         new_list_of_tuples.append(ts)
        # return new_list_of_tuples

    def insertCountrydata(self, data: dict):
        """
        字典插入
        :param data:
        :return:
        """
        with self.sqlite3.conn:  # 添加事务
            self.sqlite3.insert_data(self.table_name, data)

    def insertCountryByTuple(self, data: tuple, headers=None):
        """
        元组插入
        :param headers:
        :param data:
        :return:
        """
        with self.sqlite3.conn:
            self.sqlite3.insert_data_tuple(self.table_name, data, headers)

    def batchInsertCountryByTuple(self, data, headers=None):
        """
        元组批量插入
        :param headers:
        :param data: 需要保存的元组数据
        :return:
        """
        with self.sqlite3.conn:
            self.sqlite3.batch_insert_data_by_tuple(self.table_name, data, headers)

    def selectLikeInfoByCountry(self, country, query_columns=None):
        """
        根据模糊查询
        :return:
        """
        condition = f" country like '%{country}%' "
        return self.sqlite3.select_data(self.table_name, columns=query_columns, condition=condition)

    def selectInfoByCountry(self, country, query_columns=None):
        """
        根据模糊查询
        :return:
        """
        condition = f" country = '{country}' "
        return self.sqlite3.select_data(self.table_name, columns=query_columns, condition=condition)

    def selectCountryByCountrys(self, countryList, query_columns=None):
        """
        根据bins查询
        :return:
        """
        countrys = tuple(countryList)
        if len(countrys) == 1:
            condition = f" country = '{countrys[0]}' "
        else:
            condition = f" country in {countrys} "
        return self.sqlite3.select_data(self.table_name, columns=query_columns, condition=condition)

    def batchDelByCountrys(self, bins):
        """
         批量删除BINS
        :param bins: bin的集合
        :return:
        """
        if bins is None or len(bins) == 0:
            return
        binlist = tuple(bins)
        if len(binlist) == 1:
            condition = f" country = '{binlist[0]}' "
        else:
            condition = f" country in {binlist} "
        with self.sqlite3.conn:  # 添加事务
            self.sqlite3.delete_data(table_name=self.table_name, condition=condition)

    def selectExportData(self, startTime, endTime):
        """
        导出数据
        :param startTime: 开始时间
        :param endTime: 结束时间
        :return:
        """
        conditon = None
        if startTime is not None and endTime is not None:
            conditon = f" update_time >= '{startTime}' and update_time <= '{endTime}' "
        return self.sqlite3.select_data_by_limit(self.table_name, condition=conditon)